"""
@Author: sws
@Data: 2020/12/20 下午15:36
一键生成对应美团的公明收益数据库执行脚本
"""

import xlrd
import pyodbc

#测试站
# server = "114.115.168.236,1666"    # 连接服务器地址
# user = "sa"               # 连接帐号
# password = "Wang@Dong@jj88Test"           # 连接密码
# db = "juanjuantest"

#备用站
server = "114.115.220.137,1688"    # 连接服务器地址
user = "zhukabao"               # 连接帐号
password = "Wang@Dong@jj88Prd"           # 连接密码
db = "juanjuan"


class ReadExcel():
    def __init__(self, excelPath, sheetName="Sheet1"):
        self.data = xlrd.open_workbook(excelPath)
        self.table = self.data.sheet_by_name(sheetName)
        # # 获取第一行作为key值
        # self.keys = self.table.row_values(0)

        # 获取总行数
        self.rowNum = self.table.nrows
        # 获取总列数
        self.colNum = self.table.ncols

    def dict_data(self):
        f ='D:/py/公明收益.txt'
        file = open(f, 'w')#清空数据
        file.write('--公明收益自动生成\n')
        print('建立文本文件:公明收益')

        conn_info = 'DRIVER={SQL Server};DATABASE=%s;SERVER=%s;UID=%s;PWD=%s' % (db, server, user, password)
        conn = pyodbc.connect(conn_info)
        cur = conn.cursor()
        if not cur:
            raise (NameError, '数据库连接失败')
        else:
            print("数据库连接成功")
        i = 0
        if self.rowNum <= 1:
            print("没有数据")
        else:
            for j in range(2,self.rowNum):

                list = ["\\", "|", "/", "-"]
                hotel_name = '注意这个酒店名为空请排查!!!!!!!!!!!'
                OtaHotelNo = '注意这个酒店的门店id为空请排查!!!!!!!!!!!'
                HotelID = '注意这个酒店的酒店id为空请排查!!!!!!!!!!!'
                if self.table.row_values(j)[1] != '':
                    #把酒店名提取出来
                    hotel_name = str(self.table.row_values(j)[1])
                else:
                    i =1
                if self.table.row_values(j)[2] != '':
                    #把门店id提取出来
                    OtaHotelNo = int(self.table.row_values(j)[2])
                else:
                    i = 1
                if self.table.row_values(j)[3] != '':
                    # 提取美团后台EB账户，并转为蜂巢中的酒店id
                    cell = self.table.row_values(j)[3]
                    if isinstance(cell,float):  # 如果是浮点型
                        cell = int(cell)
                    else:
                        cell = cell.rstrip()#去除右边的空格，2边空格是strip
                    cur.execute(f"SELECT TOP 1 h.Id FROM Hotels h LEFT JOIN GroupPurchaseMember gr on h.XgjHotelId=gr.ChannelBranchId WHERE gr.UserName=\'{cell}\'")
                    row = cur.fetchone()
                    if row:
                        HotelID = row[0]
                    else:
                        HotelID = '注意这个酒店的酒店id为空请排查!!!!!!!!!!!'
                        i = 1
                else:
                    i = 1
                #a是追加内容，开始写入文本
                file = open(f,'a')
                file.write(f'\n update  [juanjuan].[dbo].[HotelChannel] set ProfitNewsAuthorization =1,'
                           f'PeersDynamicAuthorization=1,OtaHotelNo= \'{OtaHotelNo}\''
                           f' where Channel=2 and HotelID={HotelID}  --{hotel_name}')
                #这个是转圈圈加载
                index = j % 4
                print("\r程序正在运行 {}".format(list[index]), end="")

        if i ==0:
            print('\r搞定')
        else:
            print('\r虽然搞定了但是存在没有找到的情况，请排查（可能是美团账户或门店id填写反了或错了）')


if __name__ == "__main__":
    filepath = "D:/py/美团公明收益提报2021.2修正 (1).xlsx"
    ReadExcel(filepath).dict_data()
